package org.linsoho.xmlupdate;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author lxb <linxiaobing@syncpo.com>
 *
 */
public class ServiceHelper {

    private static Logger LOG = LoggerFactory.getLogger(ServiceHelper.class);

    private static DBHelper db = DBHelper.getInstance();
    
    public static ResultSet select(String name, String version) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT * "                                )
          .append(  "FROM public.print_style_template_ver "  )
          .append( "WHERE print_style_template_ouid = "      )
          .append(     "(SELECT ouid "                       )
          .append(        "FROM public.print_style_template ")
          .append(       "WHERE name = '{0}') "              )
          .append(   "AND app_version = '{1}' "              );
        String sql = replace(sb.toString(), name, version);
        LOG.debug("sql:", sql);
        try{
            ResultSet result = db.query(sql);
            return result;
        } finally {
            db.close();
        }
    }
    
    public static boolean update(String base64, String name, String version) {
        Date date = new Date();
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        
        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE public.print_style_template_ver "   )
          .append(   "SET template = decode('{0}', 'base64') ")
          .append(     ", ver_uuid = '{1}' "                  )
          .append(     ", upd_who = 'tools' "                 )
          .append(     ", upd_time = '{2}' "                  )
          .append( "WHERE print_style_template_ouid = "       )
          .append(      "(SELECT ouid "                       )
          .append(         "FROM public.print_style_template ")
          .append(        "WHERE name = '{3}') "              )
          .append(   "AND app_version = '{4}' "               );
        String uuid = UuidUtil.generate22UUID();
        String sql = replace(sb.toString(), base64, uuid, sf.format(date), name, version);
        LOG.debug("uuid:{}, sql:{}", uuid, sql);
        try {
            return db.update(sql);
        } finally {
            db.close();
        }
    }
    
    /**
     * 插入数据
     * @param base64
     * @param name
     * @param version
     */
    public static boolean insert(String base64, String name, String version) throws Exception {
        try {
            StringBuilder sb = new StringBuilder();
            sb.append(      "SELECT ouid "                       )
              .append(        "FROM public.print_style_template ")
              .append(       "WHERE name = '{0}'"                );
            String sql = replace(sb.toString(), name);
            ResultSet result = db.query(sql);
            if (result.next()) {
                sb = new StringBuilder();
                sb.append("INSERT INTO public.print_style_template_ver"    )
                  .append( " (ouid,print_style_template_ouid,app_version," )
                  .append(   "template,status_flag,status_time,crt_who,"   )
                  .append(   "crt_time,upd_who,upd_time,ver_uuid"          )
                  .append(   ") values ('{0}','{1}','{2}',decode('{3}', "  )
                  .append(             "'base64'),'{4}','{5}','{6}','{7}',")
                  .append(             "'{8}','{9}','{10}')"               );
                String uuid = UuidUtil.generate22UUID();
                String verUuid = UuidUtil.generate22UUID();
                Date date = new Date();
                SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                String templateOuid = result.getString("ouid");
                System.out.println("uuid : " + uuid);
                LOG.debug("uuid:{}, sql:{}", uuid, sql);
                sql = replace(sb.toString(),
                        uuid,
                        templateOuid,
                        version,
                        base64,
                        "Y",
                        sf.format(date),
                        "tools",
                        sf.format(date),
                        "tools",
                        sf.format(date),
                        verUuid
                        );
                System.out.println(sql);
                return db.update(sql);
            } else {
                throw new Exception("在print_style_template表里找不到此模版");
            }
        } finally {
            db.close();
        }
    }
    
    private static String replace(String sql, String...args) {
        int len = args.length;
        for (int i = 0; i < len; i++) {
            sql = sql.replace("{" + i + "}", args[i]);
        }
        return sql;
    }
}
